/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx.db;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

/**
 * <p>Project: jdemo202203 - PreDemo
 * <p>Powered by webrx On 2022-11-01 09:09:01
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */
public class PreDemo {
    Connection conn = null;

    @Test
    void page() {
        try {
            //计算多少页
            int currpage = -2;
            //每页多少条
            int pagesize = 5;

            PreparedStatement pst = conn.prepareStatement("select count(*) from student");
            ResultSet rs = pst.executeQuery();
            rs.next();
            //总记录数
            int recordcount = rs.getInt(1);

            //计算总页数
            int pagecount = recordcount % pagesize == 0 ? recordcount / pagesize : recordcount / pagesize + 1;
            int pc = (int) Math.ceil(1.0 * recordcount / pagesize);

            //判断当前页的合理性
            if (currpage > pagecount) currpage = pagecount;
            if (currpage < 1) currpage = 1;

            //显示当前页的数据
            pst = conn.prepareStatement("select id,name,sdept from student order by id desc limit ?,?");
            pst.setInt(1, currpage * pagesize - pagesize);
            pst.setInt(2, pagesize);
            rs = pst.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }


            String pinfo = String.format("第 \033[31m%d\033[0m 页，共 %d 页，每页 %d 条，共 %d 条", currpage, pagecount, pagesize, recordcount);
            System.out.println(pinfo);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    void tbs() {
        try {
            PreparedStatement pst = conn.prepareStatement("show tables");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    void dbs() {
        try {
            PreparedStatement pst = conn.prepareStatement("show databases");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    void info() {
        try {
            PreparedStatement pst = conn.prepareStatement("select @@version,@@port,@@hostname,@@basedir,@@datadir,now(),uuid(),uuid_short()");
            ResultSet rs = pst.executeQuery();
            rs.next();
            System.out.println(rs.getString(1));
            System.out.println(rs.getString(2));
            System.out.println(rs.getString(3));
            System.out.println(rs.getString(4));
            System.out.println(rs.getString(5));
            System.out.println(rs.getString(6));
            System.out.println(rs.getString(7));
            System.out.println(rs.getString(8));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    void query() {
        try {
            PreparedStatement pst = conn.prepareStatement("select * from student order by id desc");

            //ResultSet 结果集，是一个接口，有游标控制，.next() 判断有没有记录，如果有就返回true
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @BeforeEach
        //@Test
    void init() throws IOException {
        //加载src/main/resources/db.propertie文件配置 信息
        InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        prop.load(is);
        String driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
        String url = prop.getProperty("db.url", "jdbc:mysql:/");
        String user = prop.getProperty("db.username", "root");
        String pwd = prop.getProperty("db.password", "");
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @AfterEach
    void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
